package com.apes.fn.business.fixedLoss.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

/**
 * @ClassName : LossQuotation
 * @Description : 定损报价单原生SQL查询
 * @Author : WSJ
 * @Date: 2020-03-20 19:00
 */
@Component
public class LossQuotationDao extends ApesDao {
    /**
     * 根据基地或者门店查询车险报案单
     * @param param
     * @return
     */
    public JSONArray findCarAccidentByDeptOrBase(JSONObject param) {

        String sSQL = "SELECT A.ID,A.CAR_NUMBER,A.REPORT_NUMBER,A.DEPT_ID,  " +
                "       DATE_TIME(A.CREATE_DATE) CREATE_DATE,  " +
                "       (SELECT NAME FROM MAS_DEPT  WHERE A.DEPT_ID = ID) DEPT_NAME     " +
                " FROM SER_CAR_ACCIDENT A, SER_CAR_EVALUATION B   " +
                " WHERE A.ID = B.CAR_ACCIDENT_ID " +
                "   AND ( (A.DEPT_ID = :DEPT AND B.FIXED_LOSS_TYPE = 'CarEvaluation.fixedLossType.A' " +
                "           AND EXISTS (SELECT 1 FROM FN_PICK_UP_CAR C WHERE C.DEPT_ID = A.DEPT_ID " +
                "           AND A.CAR_NUMBER = C.LICENSE_NUMBER AND C.STATE = 'approve'))   " +
                "        OR (B.BASE_ID = :DEPT AND B.FIXED_LOSS_TYPE ='CarEvaluation.fixedLossType.B'" +
                "             AND EXISTS (SELECT 1 FROM FN_INNER_PICK_DEL_UP_CAR  D WHERE D.OTHER_SIDE_DEPT_ID = B.BASE_ID " +
                "                           AND D.LICENSE_NUMBER = A.CAR_NUMBER AND D.STATE = 'get' ) ) ) " +
                "   AND A.STATE IN ('inStore','fixedLoss','baseReported') ";
        if(param.get("ID") != null ) sSQL += "AND A.ID = '" + param.getString("ID") + "'";
        if(param.get("CAR_NUMBER") != null ) sSQL += " AND A.CAR_NUMBER =:CAR_NUMBER "  ;
        if(param.get("CREATE_DATE_START") != null) sSQL += " AND A.CREATE_DATE >=:CREATE_DATE_START  " ;
        if(param.get("CREATE_DATE_END") != null) sSQL += " AND A.CREATE_DATE <=:CREATE_DATE_END  " ;
        return toJSONArray(find(sSQL, param));
    }


    /**
     * 查询当天车险销售数据
     * @param param
     * @return
     */
    public JSONArray queryInsuranceSale(JSONObject param) {

        String sql = "SELECT STATISTIC_DATE,DEPT_ID,SUM(INSURANCE_AMOUNT)INSURANCE_AMOUNT,SUM(SALE_AMOUNT)SALE_AMOUNT  " +
                "FROM (  " +
                "  SELECT GET_DATE(A.RECEIPT_DATE)STATISTIC_DATE,A.DEPT DEPT_ID,  " +
                "         SUM(A.COMMERCIAL_INSURANCE + A.COMPULSORY_INSURANCE)INSURANCE_AMOUNT,  " +
                "         0 SALE_AMOUNT   " +
                "  FROM INSURANCE_ORDER A   " +
                "  WHERE A.RECEIPT_DATE IS NOT NULL AND A.RECEIPT_DATE>=:DATE  " +
                "        AND A.RECEIPT_DATE <:DATE +1   " +
                "  GROUP BY GET_DATE(A.RECEIPT_DATE),A.DEPT   " +
                "  UNION ALL   " +
                "  SELECT GET_DATE(A.REFUND_DATE)STATISTIC_DATE,A.DEPT DEPT_ID,  " +
                "         SUM(A.COMMERCIAL_INSURANCE + A.COMPULSORY_INSURANCE) * -1 INSURANCE_AMOUNT,  " +
                "         0 SALE_AMOUNT   " +
                "  FROM INSURANCE_ORDER A   " +
                "  WHERE A.REFUND_DATE IS NOT NULL AND A.REFUND_DATE>=:DATE   " +
                "        AND A.REFUND_DATE <:DATE +1  " +
                "  GROUP BY GET_DATE(A.REFUND_DATE),A.DEPT   " +
                "  UNION ALL  " +
                "  SELECT GET_DATE(A.DATE_APPROVE)STATISTIC_DATE,A.DEPT_ID,0 INSURANCE_AMOUNT,  " +
                "         SUM(A.AMT_TOTAL * B.DIVIDEND_STORE)SALE_AMOUNT  " +
                "  FROM RETAIL_ORDER A ,SER_LOSS_QUOTATION B  " +
                "  WHERE  A.STATE ='vouched' AND A.SOURCE = B.ID  " +
                "      AND A.DATE_APPROVE >=:DATE AND A.DATE_APPROVE < :DATE + 1  " +
                "      AND A.CASE_TYPE NOT IN ('001493','001490')  " +
                "      AND A.RETURN_TYPE IS NULL  " +
                "  GROUP BY GET_DATE(A.DATE_APPROVE),A.DEPT_ID  )  " +
                "GROUP BY STATISTIC_DATE,DEPT_ID ";
        return toJSONArray(find(sql, param));
    }

    public JSONArray queryFixedLossLimitDetail(JSONObject param) {

        String sql = "SELECT DEPT_ID,  " +
                "       SUM(INSURANCE_AMOUNT) INSURANCE_AMOUNT,  " +
                "       SUM(USED_QUOTA)  " +
                "                             USED_QUOTA,  " +
                "       SUM(TRANSFORM_QUOTA)  TRANSFORM_QUOTA,  " +
                "       SUM(TARGET_AMOUNT)    TARGET_AMOUNT,  " +
                "       SUM(OVERDUE_AMOUNT)   OVERDUE_AMOUNT  " +
                "FROM (  " +
                "         SELECT A.DEPT_ID,  " +
                "                SUM(A.INSURANCE_AMOUNT) INSURANCE_AMOUNT,  " +
                "                SUM(A.USED_QUOTA)       USED_QUOTA,  " +
                "                SUM(A.TRANSFORM_QUOTA)  TRANSFORM_QUOTA,  " +
                "                0                       TARGET_AMOUNT,  " +
                "                0                       OVERDUE_AMOUNT  " +
                "         FROM FIXED_LOSS_LIMIT_DETAIL A  " +
                "         WHERE A.STATISTIC_DATE >= :firstDayOfMonth  " +
                "           AND A.STATISTIC_DATE < :lastDayOfMonth+ 1  " +
                "         GROUP BY A.DEPT_ID  " +
                "  " +
                "         UNION ALL  " +
                "         SELECT D.DEPT_ID,  " +
                "                0                     INSURANCE_AMOUNT,  " +
                "                0                     USED_QUOTA,  " +
                "                0                     TRANSFORM_QUOTA,  " +
                "                SUM(D.TARGET_AMOUNT)  TARGET_AMOUNT,  " +
                "                SUM(D.OVERDUE_AMOUNT) OVERDUE_AMOUNT  " +
                "         FROM (  " +
                "                  SELECT B.DEPT_ID, SUM(B.TARGET_AMOUNT) TARGET_AMOUNT, 0 OVERDUE_AMOUNT  " +
                "                  FROM BUSINESS_STRATEGY A,  " +
                "                       BUSINESS_STRATEGY_ITEM B  " +
                "                  WHERE A.ID = B.BUSINESS_STRATEGY  " +
                "                    AND TRUNC(A.VALID_PERIOD_END) >= :taskDate  " +
                "                    AND TRUNC(A.VALID_PERIOD_START) <= :taskDate  " +
                "  " +
                "                  GROUP BY B.DEPT_ID  " +
                "                  UNION ALL  " +
                "                  SELECT B.DEPT_ID, 0 TARGET_AMOUNT, SUM(B.TARGET_AMOUNT) * -1 OVERDUE_AMOUNT  " +
                "                  FROM BUSINESS_STRATEGY A,  " +
                "                       BUSINESS_STRATEGY_ITEM B  " +
                "                  WHERE A.ID = B.BUSINESS_STRATEGY  " +
                "                    AND TRUNC(A.VALID_PERIOD_END) + 1 = :taskDate  " +
                "                  GROUP BY B.DEPT_ID) D  " +
                "         GROUP BY D.DEPT_ID)  " +
                "GROUP BY DEPT_ID  ";
        return toJSONArray(find(sql, param));
    }
    public JSONArray queryLossInfo(JSONObject param) {
        String sql = "SELECT A.ID,D.NAME CATEGORY_NAME,C.NAME PRODUCT_ID,B.SALE_QTY,B.SALE_PRICE,B.SALES_AMOUNT,FIXED_LOSS_PRICE,FIXED_LOSS_AMOUNT,b.REFER_TAX,b.REMARKS  " +
                "FROM SER_LOSS_QUOTATION A,SER_LOSS_QUOTATION_ITEM B,MAS_PRODUCT C,MAS_CATEGORY D,SER_CAR_ACCIDENT e  " +
                "WHERE A.ID = B.LOSS_QUOTATION_ID  " +
                "AND C.ID= B.PRODUCT_ID  " +
                "AND C.CATEGORY_ID = D.ID  " +
                "and a.CAR_ACCIDENT =e.ID  " +
                "and e.id = :key";
        return toJSONArray(find(sql, param));
    }
    public JSONArray queryLossReceivedItemInfo(JSONObject param) {
        String sql = "select to_char(c.RECEIVED_DATE,'yyyy-MM-dd')RECEIVED_DATE,c.RECEIVED_AMOUNT,d.DESCRIPTION,SERIAL_NUMBER,LOSS_QUOTATION,c.OPERATOR,to_char(c.CREATE_DATE,'yyyy-MM-dd')CREATE_DATE\n" +
                "FROM SER_CAR_ACCIDENT A,SER_LOSS_QUOTATION B ,SER_LOSS_RECEIVED_ITEM C,MAS_ENUMERATION D\n" +
                "WHERE A.ID =B.CAR_ACCIDENT\n" +
                "AND B.ID = C.LOSS_QUOTATION\n" +
                "AND D.ID = C.ACCOUNT_TYPE " +
                "AND A.ID = :key";
        return toJSONArray(find(sql, param));
    }

    public JSONArray querySettleAmount(JSONObject param) {

        String sql = "select * from (select *\n" +
                "from (select  max(a.POST_DATE)                                             sort,\n" +
                "                   to_char(A.POST_DATE, 'yyyy-mm-dd')                         POST_DATE,\n" +
                "                   MAX(A.INNER_BUSINESS_TYPE)                                 INNER_BUSINESS_TYPE,\n" +
                "                   A.ID,\n" +
                "                   avg(A.ACTUAL_TOTAL)                                        ACTUAL_TOTAL,\n" +
                "                   nvl(avg(d.ADJUST_AMOUNT), 0)                           ADJUSTMENT_AMOUNT,\n" +
                "                   nvl(avg(d.ADJUST_AMOUNT), 0) + avg(ACTUAL_TOTAL)\n" +
                "                                                                              TOTAL,\n" +
                "                   nvl(avg(d.ADJUST_AMOUNT), 0) + avg(ACTUAL_TOTAL) - nvl(SUM(b.APPORTION_AMOUNT), 0)\n" +
                "                                                                              storeWaitReceived,\n" +
                "                   nvl(avg(d.ADJUST_AMOUNT), 0) + avg(ACTUAL_TOTAL) - nvl(SUM(case\n" +
                "                                                                                    when c.ACCOUNT_TYPE\n" +
                "                                                                                        <> '002033'\n" +
                "                                                                                        THEN b.APPORTION_AMOUNT\n" +
                "                                                                                    ELSE b.PAYMENT_AMOUNT END), 0) \n" +
                "                                                                              platformWaitReceived,\n" +
                "                   nvl(SUM(case\n" +
                "                               when c.ACCOUNT_TYPE = '002033' THEN b.APPORTION_AMOUNT\n" +
                "                               ELSE\n" +
                "                                   0 END), 0) - nvl(sum(b.PAYMENT_AMOUNT), 0) storeWaitToPrepare,\n" +
                "                   max(b.REMARK)                                              REMARK\n" +
                " from RETAIL_ORDER a " +
                "   left join ser_received_apportion b on A.ID = B.APPORTION_ORDER " +
//                "and b.TYPE IN ('RO','RT') " +
                "   left join SER_LOSS_QUOTATION d on d.id = b.LOSS_QUOTATION_ID and b.TYPE = 'DT' " +
                "   left join ser_loss_received_item C on C.ID = B.LOSS_RECEIVED_ITEM\n" +
                " WHERE a.SOURCE = :id" +
//                " and a.SOURCE_BUSINESS_TYPE<>'LossSaleAdjust'\n" +
                " GROUP BY a.ID,A.POST_DATE\n" +
//                " UNION all\n" +
//                " select POST_DATE         sort,\n" +
//                "                   to_char(A.POST_DATE, 'yyyy-mm-dd')\n" +
//                "                                     POST_DATE,\n" +
//                "                   A.INNER_BUSINESS_TYPE,\n" +
//                "                   A.ID,\n" +
//                "                   0,\n" +
//                "                   SUM(B.ADJUSTMENT_AMOUNT),\n" +
//                "                   SUM(B.ADJUSTMENT_AMOUNT) TOTAL,\n" +
//                "                   SUM(case\n" +
//                "                       when\n" +
//                "                           ADJUSTMENT_AMOUNT > 0 then ADJUSTMENT_AMOUNT\n" +
//                "                       else 0 end )-SUM(b.APPORTION_AMOUNT),\n" +
//                "                   SUM(case\n" +
//                "                       when ADJUSTMENT_AMOUNT > 0 then ADJUSTMENT_AMOUNT\n" +
//                "                       else 0 end)-SUM(b.APPORTION_AMOUNT),\n" +
//                "                   0,\n" +
//                "                   MAX(b.REMARK)\n" +
//                "            from RETAIL_ORDER a,\n" +
//                "                 ser_received_apportion b\n" +
//                "            where b.TYPE IN ('DT','RT')\n" +
//                "              AND A.ID\n" +
//                "                = B.APPORTION_ORDER\n" +
//                "              and a.SOURCE = :id\n" +
//                "          GROUP BY A.POST_DATE,A.INNER_BUSINESS_TYPE,A.ID" +
                ")" +
                " order by sort asc" +
                ") A " +
                " order by id desc"
                ;
        return toJSONArray(find(sql, param));
    }


}
